package nz.ac.massey.doi_information_extractor.database;

import java.sql.*;
import java.util.ArrayList;

import nz.ac.massey.doi_information_extractor.data.*;

public class DatabaseImplementation implements DatabaseInterface {

	static Connection connection;

	/**
	 * Creates database connection, and setups database if necessary.
	 *
	 * @param url
	 */
	public DatabaseImplementation(String url) throws SQLException, ClassNotFoundException
	{
			connection = getConnection(url);

			PreparedStatement statement = connection.prepareStatement("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name");
			ResultSet resultSet = statement.executeQuery();
			int tableCount = 0;
			while(resultSet.next()){
				tableCount++;
			}
			resultSet.close();
			statement.close();
			if(tableCount == 1 || tableCount == 0)
			{
				statement = connection.prepareStatement("CREATE TABLE author (authorId INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE," +
						"authorFirstName VARCHAR, authorLastName Varchar, organisation varchar, department varchar, authorType varchar," +
						"userId varchar)");
				statement.executeUpdate();
				statement.close();
				statement = connection.prepareStatement("CREATE TABLE publication (pubId INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, description varchar," +
						"contributionType varchar, pageNumbers Varchar, pubTitle varchar, qualityAssured boolean, confidential boolean," +
						"researchOrProfCom boolean, volumeNumber varchar, articleTitle varchar, pubDate varchar, url varchar, issn varchar," +
						"doi varchar,publisher varchar, keywords varchar, conferenceLocation varchar, conferenceDates varchar, publisherLocation varchar," +
						"editors varchar, edition varchar, journalNumber varchar, rimsId varchar)");
				statement.executeUpdate();
				statement.close();
				statement = connection.prepareStatement("CREATE TABLE authors (pubId INTEGER ," +
						"authorId INTEGER , staffSubmitter INTEGER, indexValue INTEGER, PRIMARY KEY(authorId,pubId),UNIQUE(authorId,pubId)) ");
				statement.executeUpdate();
				statement.close();
			}
	}

	/**
	 * Closes Database connection
	 *
	 */
	public void closeConnection()
	{
		try {
			connection.close();
		} catch (SQLException e) {
			return;
		}
	}


	/**
	 * Creates database connection.
	 *
	 * @param url
	 * @throws SQLException, ClassNotFoundException
	 */
	public Connection getConnection(String url) throws SQLException, ClassNotFoundException {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection("jdbc:sqlite:" + url);

		return connection;
	}

	/**
	 * Gets a list of all the DOIs in the database
	 *
	 * @return a list of DOI's
	 * @throws SQLException
	 */
	@Override
	public String[] getDoiList() throws SQLException
	{
		ArrayList<String> doiList = new ArrayList<String>();
		String[] stringList;

			PreparedStatement statement = connection.prepareStatement("Select doi From publication Order by pubDate");
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next())
			{
				doiList.add(resultSet.getString("doi"));
				//Assign it the appropriate properties
			}
			resultSet.close();
			statement.close();
			int i = doiList.size() -1;
			stringList = new String[doiList.size()];
			while(i >= 0)
			{
				stringList[i] = (String) doiList.get(i);
				i--;
			}


		return stringList;
	}

	/**
	 * Gets a list of all the authors in the database
	 *
	 * @return a list of authors
	 */
	@Override
	public ArrayList<Author> getAuthors() throws SQLException
	{
		ArrayList<Author> authorList = new ArrayList<Author>();
			PreparedStatement statement = connection.prepareStatement("Select * From author");
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next())
			{
				Author author = new Author();
				author.setAuthorId(resultSet.getInt("authorId"));
				author.setAuthorFirstName(resultSet.getString("authorFirstName"));
				author.setAuthorLastName(resultSet.getString("authorLastName"));
				author.setOrganisation(resultSet.getString("organisation"));
				author.setDepartment(resultSet.getString("department"));
				author.setAuthorType(resultSet.getString("authorType"));
				author.setUserId(resultSet.getString("userId"));
				authorList.add(author);
			}
			resultSet.close();
			statement.close();
		return authorList;
	}

	/**
	 * Reads a publication from the database using the DOI as a key
	 * @param doi the DOI to find the publication for
	 *
	 * @return The publication that relates to the DOI
	 * @throws SQLException
	 */
	public Publication read(String doi) throws SQLException
	{
		Publication pub = new Publication();
			AuthorList authorList = new AuthorList();
			PreparedStatement statement = connection.prepareStatement("Select author.authorID,authorFirstName,authorLastName,organisation,department,userId,indexValue From author,authors,publication where author.authorID = authors.authorID AND publication.pubID = authors.pubID AND doi = ? Group By author.authorID,authorFirstName,authorLastName,organisation,department,userId Order By indexValue ");
			statement.setString(1, doi);
			statement.execute();
			ResultSet resultSet = statement.executeQuery();

			while(resultSet.next())
			{
				Author author = new Author();
				author.setAuthorId(resultSet.getInt("authorID"));
				author.setAuthorFirstName(resultSet.getString("authorFirstName"));
				author.setAuthorLastName(resultSet.getString("authorLastName"));
				author.setOrganisation(resultSet.getString("organisation"));
				author.setDepartment(resultSet.getString("department"));
				author.setUserId(resultSet.getString("userId"));
				authorList.add(author);
			}
			resultSet.close();
			statement.close();
			int submitter = 0;


			statement = connection.prepareStatement("Select staffSubmitter,indexValue From authors,publication where publication.pubId = authors.pubID AND doi = ? Group By staffSubmitter Order By staffSubmitter");
			statement.setString(1, doi);
			resultSet = statement.executeQuery();
			while(resultSet.next())
			{
				submitter = resultSet.getInt(1);
			}
			resultSet.close();
			statement.close();
			authorList.setStaffSubmitter(submitter);


			statement = connection.prepareStatement("Select * from publication where doi = ?");
			statement.setString(1, doi);
			resultSet = statement.executeQuery();
			while(resultSet.next())
			{
				pub = new Publication();
				pub.setPublicationId(resultSet.getInt("pubID"));
				pub.setDescription(resultSet.getString("description"));
				pub.setPublicationType(resultSet.getString("contributionType"));
				pub.setPageNumbers(resultSet.getString("pageNumbers"));
				pub.setPublicationTitle(resultSet.getString("pubTitle"));
				pub.setQualityAssured(resultSet.getBoolean("qualityAssured"));
				pub.setConfidential(resultSet.getBoolean("confidential"));
				pub.setResearchOrProfCom(resultSet.getBoolean("researchOrProfCom"));
				pub.setVolumeNumber(resultSet.getString("volumeNumber"));
				pub.setArticleTitle(resultSet.getString("articleTitle"));
				pub.setDate(resultSet.getString("pubDate"));
				pub.setUrl(resultSet.getString("url"));
				pub.setIssn(resultSet.getString("issn"));
				pub.setDoi(resultSet.getString("doi"));
				pub.setAuthors(authorList);
				pub.setPublisher(resultSet.getString("publisher"));
				pub.setKeywords(resultSet.getString("keywords"));
				pub.setConferenceLocation(resultSet.getString("conferenceLocation"));
				pub.setConferenceDates(resultSet.getString("conferenceDates"));
				pub.setPublisherLocation(resultSet.getString("publisherLocation"));
				pub.setEditors(resultSet.getString("editors"));
				pub.setEdition(resultSet.getString("Edition"));
				pub.setJournalNumber(resultSet.getString("journalNumber"));
				pub.setRimsId(resultSet.getString("rimsId"));
			}
			resultSet.close();
			statement.close();

		return pub;
	}

	/**
	 * Writes a author to the database, if a entry exists update it
	 * else add a new author to the database
	 *
	 * @param author The author to write to the database
	 * @throws SQLException
	 */
	@Override
	public void writeAuthor(Author author) throws SQLException
	{
		int authorId = -1;

			Boolean exists = false;
			PreparedStatement statement = connection.prepareStatement("Select authorFirstName from author" +
					" where authorFirstName = ? AND authorLastName = ? AND organisation = ? ");
			statement.setString(1, author.getAuthorFirstName());
			statement.setString(2, author.getAuthorLastName());
			statement.setString(3, author.getOrganisation());
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next())
			{
				try
				{
					resultSet.getString("authorFirstName");
					exists = true;
				}
				catch(NullPointerException npe)
				{
					exists = false;
				}
			}
			resultSet.close();
			statement.close();

			if(exists)
			{
				statement = connection.prepareStatement("UPDATE author SET department = ?, authorType = ?, userId = ? Where authorFirstName = ? AND authorLastName = ? AND organisation = ? ");
				statement.setString(1, author.getDepartment());
				statement.setString(2, author.getAuthorType());
				statement.setString(3, author.getUserId());
				statement.setString(4, author.getAuthorFirstName());
				statement.setString(5, author.getAuthorLastName());
				statement.setString(6, author.getOrganisation());
				statement.execute();
				statement.close();
			}
			else
			{
				statement = connection.prepareStatement("INSERT INTO author (authorFirstName,authorLastName,organisation,department,authorType,userId) VALUES(?,?,?,?,?,?)");
				statement.setString(1, author.getAuthorFirstName());
				statement.setString(2, author.getAuthorLastName());
				statement.setString(3, author.getOrganisation());
				statement.setString(4, author.getDepartment());
				statement.setString(5, author.getAuthorType());
				statement.setString(6, author.getUserId());
				statement.execute();
				statement.close();
			}

			statement = connection.prepareStatement("Select authorId From author Where authorFirstName = ? And authorLastName = ? And Organisation = ?");
			statement.setString(1, author.getAuthorFirstName());
			statement.setString(2, author.getAuthorLastName());
			statement.setString(3, author.getOrganisation());
			resultSet = statement.executeQuery();
			while(resultSet.next())
			{
				authorId = resultSet.getInt("authorId");
			}
			resultSet.close();
			statement.close();
		author.setAuthorId(authorId);
	}

	/**
	 * Writes a publication to the database, if a entry exists update it
	 * else add a new publication to the database
	 *
	 * @param publication The publication to write to the database
	 * @throws SQLException
	 */
	@Override
	public void write(Publication pub) throws SQLException
	{
		String doi = pub.getDoi();
		int key = 0;
			boolean exists = false;
			PreparedStatement statement = connection.prepareStatement("Select doi From publication Where doi = ?");
			statement.setString(1, doi);
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next())
			{
				try
				{
					resultSet.getString("doi");
					statement.close();
					exists = true;
				}
				catch(NullPointerException npe)
				{
					exists = false;
				}
			}
			resultSet.close();
			statement.close();


			if(exists)
			{
				//Update Publication Data
				statement = connection.prepareStatement(
						"UPDATE PUBLICATION SET description = ?, contributionType = ? " +
						", pageNumbers = ?, pubTitle = ?, qualityAssured = ?, confidential = ? " +
						", researchOrProfCom = ?, volumeNumber = ?, articleTitle = ?, pubDate = ? " +
						", url = ?, issn = ?, publisher = ?, keywords = ? " +
						", conferenceLocation = ?, conferenceDates = ?, publisherLocation = ? " +
				", editors = ?, edition = ?, journalNumber = ?, rimsId = ? WHERE doi = ? ");
				statement.setString(1, pub.getDescription());
				statement.setString(2, pub.getPublicationType());
				statement.setString(3, pub.getPageNumbers());
				statement.setString(4, pub.getPublicationTitle());
				statement.setBoolean(5, pub.getQualityAssured());
				statement.setBoolean(6, pub.getConfidential());
				statement.setBoolean(7, pub.getResearchOrProfCom());
				statement.setString(8, pub.getVolumeNumber());
				statement.setString(9, pub.getArticleTitle());
				statement.setString(10, pub.getDate());
				statement.setString(11, pub.getUrl());
				statement.setString(12, pub.getIssn());
				statement.setString(13, pub.getPublisher());
				statement.setString(14, pub.getKeywords());
				statement.setString(15, pub.getConferenceLocation());
				statement.setString(16, pub.getConferenceDates());
				statement.setString(17, pub.getPublisherLocation());
				statement.setString(18, pub.getEditors());
				statement.setString(19, pub.getEdition());
				statement.setString(20, pub.getJournalNumber());
				statement.setString(21, pub.getRimsId());
				statement.setString(22, pub.getDoi());
				statement.execute();
				statement.close();

				statement = connection.prepareStatement("Select pubId from publication where doi = ?");
				statement.setString(1, doi);
				resultSet = statement.executeQuery();
				while(resultSet.next())
				{
					pub.setPublicationId(resultSet.getInt(1));
				}
				statement.close();
				resultSet.close();

				ArrayList<Author> authors = pub.getAuthors();
				statement = connection.prepareStatement("Delete from authors where pubID = ?");
				statement.setInt(1, pub.getPublicationId());
				statement.execute();
				statement.close();

				for(int i = 0; i < authors.size(); i++)
				{
					Author author = pub.getAuthors().get(i);
					writeAuthor(authors.get(i));

					ArrayList<Author> authorsInDb = getAuthors();

					for(int j = 0; j < authorsInDb.size(); j++)
					{
						if(authorsInDb.get(j).getAuthorFirstName().equalsIgnoreCase(author.getAuthorFirstName()) && authorsInDb.get(j).getAuthorLastName().equalsIgnoreCase(author.getAuthorLastName()) && authorsInDb.get(j).getOrganisation().equalsIgnoreCase(author.getOrganisation()))
						{
							key = authorsInDb.get(j).getAuthorId();

							statement = connection.prepareStatement("INSERT INTO Authors VALUES(?,?,?,?)");
							statement.setInt(1, pub.getPublicationId());
							statement.setInt(2, key );
							statement.setInt(3, pub.getAuthors().getStaffSubmitter());
							statement.setInt(4, i + 1);
							statement.execute();
							statement.close();
						}
					}
				}

			}
			else{
				//Insert Publication Data
				statement = connection.prepareStatement("INSERT INTO publication" +
						" (description,contributionType,pageNumbers,pubTitle,qualityAssured,confidential," +
						"researchOrProfCom,volumeNumber,articleTitle,pubDate,url,issn,doi,publisher,keywords," +
						"conferenceLocation,conferenceDates,publisherLocation,editors,edition,journalNumber, rimsId)" +
						" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
				//statement.setInt(1, pub.getPublicationId());
				statement.setString(1, pub.getDescription());
				statement.setString(2, pub.getPublicationType());
				statement.setString(3, pub.getPageNumbers());
				statement.setString(4, pub.getPublicationTitle());
				statement.setBoolean(5, pub.getQualityAssured());
				statement.setBoolean(6, pub.getConfidential());
				statement.setBoolean(7, pub.getResearchOrProfCom());
				statement.setString(8, pub.getVolumeNumber());
				statement.setString(9, pub.getArticleTitle());
				statement.setString(10, pub.getDate());
				statement.setString(11, pub.getUrl());
				statement.setString(12, pub.getIssn());
				statement.setString(13, pub.getDoi());
				statement.setString(14, pub.getPublisher());
				statement.setString(15, pub.getKeywords());
				statement.setString(16, pub.getConferenceLocation());
				statement.setString(17, pub.getConferenceDates());
				statement.setString(18, pub.getPublisherLocation());
				statement.setString(19, pub.getEditors());
				statement.setString(20, pub.getEdition());
				statement.setString(21, pub.getJournalNumber());
				statement.setString(22, pub.getRimsId());
				statement.execute();
				statement.close();

				statement = connection.prepareStatement("Select pubId from publication where doi = ?");
				statement.setString(1, doi);
				resultSet = statement.executeQuery();

				while(resultSet.next())
				{
					pub.setPublicationId(resultSet.getInt(1));
				}
				resultSet.close();
				statement.close();

				ArrayList<Author> authors = pub.getAuthors();
				ArrayList<Author> authorsInDb;
				for(int i = 0; i < authors.size(); i++)
				{
					writeAuthor(authors.get(i));

					Author author = authors.get(i);
					authorsInDb = getAuthors();


					for(int j = 0; j < authorsInDb.size(); j++)
					{
						if(authorsInDb.get(j).getAuthorFirstName().equalsIgnoreCase(author.getAuthorFirstName()) && authorsInDb.get(j).getAuthorLastName().equalsIgnoreCase(author.getAuthorLastName()) && authorsInDb.get(j).getOrganisation().equalsIgnoreCase(author.getOrganisation()))
						{
							key = authorsInDb.get(j).getAuthorId();
							statement = connection.prepareStatement("INSERT INTO Authors VALUES(?,?,?,?)");
							statement.setInt(1, pub.getPublicationId());
							statement.setInt(2, key );
							statement.setInt(3, pub.getAuthors().getStaffSubmitter());
							statement.setInt(4, i + 1);
							statement.execute();
							statement.close();
						}
					}
				}
			}


	}

	/**
	 * Deletes an item from the database
	 * @param doi
	 * @throws SQLException
	 */
	@Override
	public void delete(String doi) throws SQLException
	{
		PreparedStatement statement = connection.prepareStatement("Select pubID from publication where doi = ?");
		statement.setString(1, doi);
		ResultSet resultSet = statement.executeQuery();
		int pubID = -1;
		while(resultSet.next())
		{
			pubID = resultSet.getInt("pubID");
		}
		resultSet.close();
		statement.close();
		statement = connection.prepareStatement("Delete from authors where pubID = ?");
		statement.setInt(1, pubID);
		statement.executeUpdate();
		statement.close();
		statement = connection.prepareStatement("Delete from publication where doi = ?");
		statement.setString(1, doi);
		statement.executeUpdate();
		statement.close();
	}

	/**
	 * Deletes an author from the authorList
	 * @param authorId
	 * @throws SQLException
	 */
	public void deleteFromAuthorList(int authorId) throws SQLException
	{
			PreparedStatement statement = connection.prepareStatement("Delete from authors where authorID = ?");
			statement.setInt(1, authorId);
			statement.executeUpdate();
			statement.close();
	}

	/**
	 * Deletes an author from the database
	 * @param authorId
	 * @throws SQLException
	 */
	public void deleteAuthor(int authorId) throws SQLException
	{
			PreparedStatement statement = connection.prepareStatement("Delete from authors where authorID = ?");
			statement.setInt(1, authorId);
			statement.executeUpdate();
			statement.close();
			statement = connection.prepareStatement("Delete from author where authorId = ?");
			statement.setInt(1, authorId);
			statement.executeUpdate();
			statement.close();
	}
}
